﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NLite.Data;
using System.Data;
using NLite.Data.SqlTypes;
using NLite.Data.Dialect.Function;
using NLite.Text;
using NLite.Collections;

namespace NLite.Data.Dialect
{
    public class Oracle8iDialect : Dialect
    {

        public Oracle8iDialect()
        {
            RegisterFunction(FunctionType.Case, new CaseFunctionView());
            RegisterFunction(FunctionType.Cast, new CastFunctionView());
        }

        protected override void RegisterColumnTypes()
        {
            base.RegisterColumnTypes();

            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, "CHAR(255)");
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, 2000, "CHAR($l)");
            RegisterColumnType(DbTypeCode.AnsiString, "VARCHAR2(255)");
            RegisterColumnType(DbTypeCode.AnsiString, 4000, "VARCHAR2($l)");
            RegisterColumnType(DbTypeCode.StringFixedLength, "NCHAR(255)");
            RegisterColumnType(DbTypeCode.StringFixedLength, 2000, "NCHAR($l)");
            RegisterColumnType(DbTypeCode.String, "NVARCHAR2(255)");
            RegisterColumnType(DbTypeCode.String, 4000, "NVARCHAR2($l)");

            RegisterColumnType(DbTypeCode.Guid, "RAW(16)");

            RegisterColumnType(DbTypeCode.Boolean, "NUMBER(1,0)");
            RegisterColumnType(DbTypeCode.Byte, "NUMBER(3,0)");
            RegisterColumnType(DbTypeCode.Int16, "NUMBER(5,0)");
            RegisterColumnType(DbTypeCode.Int32, "NUMBER(10,0)");
            RegisterColumnType(DbTypeCode.Int64, "NUMBER(20,0)");
            RegisterColumnType(DbTypeCode.UInt16, "NUMBER(5,0)");
            RegisterColumnType(DbTypeCode.UInt32, "NUMBER(10,0)");
            RegisterColumnType(DbTypeCode.UInt64, "NUMBER(20,0)");

            RegisterColumnType(DbTypeCode.Currency, "NUMBER(20,2)");
            RegisterColumnType(DbTypeCode.Currency, "NUMBER($pair,$s)");
            RegisterColumnType(DbTypeCode.Single, "FLOAT(24)");
            RegisterColumnType(DbTypeCode.Double, "DOUBLE PRECISION");
            RegisterColumnType(DbTypeCode.Double, 19, "NUMBER($pair,$s)");
            RegisterColumnType(DbTypeCode.Decimal, "NUMBER(19,5)");
            RegisterColumnType(DbTypeCode.Decimal, 19, "NUMBER($pair,$s)");

            RegisterColumnType(DbTypeCode.Date, "DATE");
            RegisterColumnType(DbTypeCode.DateTime, "DATE");
            RegisterColumnType(DbTypeCode.Time, "DATE");

            RegisterColumnType(DbTypeCode.Binary, "RAW(2000)");
            RegisterColumnType(DbTypeCode.Binary, 2000, "RAW($l)");
            RegisterColumnType(DbTypeCode.Blob, 2147483647, "BLOB");
            RegisterColumnType(DbTypeCode.Binary, 2147483647, "BLOB");
            RegisterColumnType(DbTypeCode.Clob, 2147483647, "CLOB");
            RegisterColumnType(DbTypeCode.AnsiString, 2147483647, "CLOB");
            RegisterColumnType(DbTypeCode.NClob, 1073741823, "NCLOB");
            RegisterColumnType(DbTypeCode.String, 1073741823, "NCLOB");
        }

        public override char OpenQuote
        {
            get
            {
                return '\"';
            }
        }

        public override char CloseQuote
        {
            get
            {
                return '\"';
            }
        }

        #region Functions
        

        [Serializable]
        private class LocateFunction : IFunctionView
        {
            private static readonly IFunctionView LocateWith2Params = new FunctionTemplateView("instr(?1, ?2)");

            private static readonly IFunctionView LocateWith3Params = new FunctionTemplateView("instr(?1, ?2, ?3)");


            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args == null)
                    throw new ArgumentNullException("args");
                if (args.Length != 2 && args.Length != 3)
                    throw new ArgumentException("'locate' function takes 2 or 3 arguments");
                if (args.Length == 2)
                    LocateWith2Params.Render(visitor, args);
                else
                    LocateWith3Params.Render(visitor, args);
            }
        }

        [Serializable]
        private class RemoveFunctionView : IFunctionView
        {
            private static readonly IFunctionView LocateWith2Params = new FunctionTemplateView("REPLACE(?1, SUBSTR(?1, ?2), '')");
            private static readonly IFunctionView LocateWith3Params = new FunctionTemplateView("REPLACE(?1, SUBSTR(?1, ?2, ?3), '')");

            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args == null)
                    throw new ArgumentNullException("args");
                if (args.Length != 2 && args.Length != 3)
                    throw new ArgumentException("'remove' function takes 2 or 3 arguments");
                if (args.Length == 2)
                    LocateWith2Params.Render(visitor, args);
                else
                    LocateWith3Params.Render(visitor, args);
            }
        }


        protected override void RegisterStringFunction()
        {
            RegisterFunction(FunctionType.String.Lower, new StandardFunctionView("lower"));
            RegisterFunction(FunctionType.String.LTrim, new StandardFunctionView("ltrim"));
            RegisterFunction(FunctionType.String.RTrim, new StandardFunctionView("rtrim"));
            RegisterFunction(FunctionType.String.Trim, new StandardFunctionView("trim"));
            RegisterFunction(FunctionType.String.Upper, new StandardFunctionView("upper"));
            RegisterFunction(FunctionType.String.Length, new StandardFunctionView("length"));
            RegisterFunction(FunctionType.String.Left, new FunctionTemplateView("substr(?1, 1, ?2)"));
            RegisterFunction(FunctionType.String.Right, new FunctionTemplateView("substr(?1, -?2)"));
            RegisterFunction(FunctionType.ToChar, new StandardFunctionView("to_char"));
            RegisterFunction(FunctionType.String.Concat, new VarArgsFunctionView("(", "||", ")"));
            RegisterFunction(FunctionType.String.SubString, new StandardFunctionView("substr"));
            RegisterFunction(FunctionType.String.IndexOf, new LocateFunction());
            RegisterFunction(FunctionType.String.Remove, new RemoveFunctionView());
            RegisterFunction(FunctionType.String.Replace, new StandardFunctionView("replace"));
        }

        protected override void RegisterMathFunction()
        {
            RegisterFunction(FunctionType.Math.Abs, new StandardFunctionView("abs"));
            RegisterFunction(FunctionType.Math.Sign, new StandardFunctionView("sign"));
            RegisterFunction(FunctionType.Math.Acos, new StandardFunctionView("acos"));
            RegisterFunction(FunctionType.Math.Asin, new FunctionTemplateView("cast(asin(?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Atan, new FunctionTemplateView("cast(atan(?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Cos, new FunctionTemplateView("cast(cos(?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Cosh, new StandardFunctionView("cosh"));
            RegisterFunction(FunctionType.Math.Exp, new FunctionTemplateView("cast(exp(?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Log, new FunctionTemplateView("cast(ln(?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Log10, new FunctionTemplateView("cast(log(10,?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Sin, new StandardFunctionView("sin"));
            RegisterFunction(FunctionType.Math.Sinh, new StandardFunctionView("sinh"));
            RegisterFunction(FunctionType.Math.Sqrt, new StandardFunctionView("sqrt"));
            RegisterFunction(FunctionType.Math.Tan, new FunctionTemplateView("cast(tan(?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Tanh, new FunctionTemplateView("cast(tanh(?1) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Round, new StandardFunctionView("round"));
            RegisterFunction(FunctionType.Math.Truncate, new StandardFunctionView("trunc"));
            RegisterFunction(FunctionType.Math.Ceiling, new StandardFunctionView("ceil"));
            RegisterFunction(FunctionType.Math.Floor, new StandardFunctionView("floor"));
            RegisterFunction(FunctionType.Math.Atan2, new FunctionTemplateView("cast(atan2(?1,?2) as NUMBER(19,9))"));
            RegisterFunction(FunctionType.Math.Power, new StandardFunctionView("power"));

            RegisterFunction(FunctionType.Math.Mod, new StandardFunctionView("mod"));

        }

       
        protected override void RegisterDateFunction()
        {
            RegisterFunction(FunctionType.DateTime.Current, new ProxyFunctionView((m, n) => m.Builder.Append("SYSDATE")));
            RegisterFunction(FunctionType.DateTime.New, new StandardFunctionView("TO_DATE"));


            RegisterFunction("current_time", new NoArgFunctionView("current_timestamp"));
            RegisterFunction("current_timestamp", new NoArgFunctionView("current_timestamp"));

            RegisterFunction(FunctionType.DateTime.Subtract, new VarArgsFunctionView("(", "-", ")"));

            RegisterFunction(FunctionType.DateTime.GetSecond, new FunctionTemplateView("to_char(?1,'ss')"));
            RegisterFunction(FunctionType.DateTime.GetMinute, new FunctionTemplateView("to_char(?1,'mi')"));
            RegisterFunction(FunctionType.DateTime.GetHour, new FunctionTemplateView("to_char(?1,'hh')"));
            RegisterFunction(FunctionType.DateTime.GetDay, new FunctionTemplateView("to_char(?1,'dd')"));
            RegisterFunction(FunctionType.DateTime.GetMonth, new FunctionTemplateView("to_char(?1,'mm')"));
            RegisterFunction(FunctionType.DateTime.GetYear, new FunctionTemplateView("to_char(?1,'yyyy')"));

            //RegisterFunction(FunctionType.DateTime.GetMillisecond, new FunctionTemplateView("datepart(millisecond, ?1)"));
            RegisterFunction(FunctionType.DateTime.GetDayOfWeek, new DayOfWeekFunctionView());
            RegisterFunction(FunctionType.DateTime.GetDayOfYear, new FunctionTemplateView("to_char(?1,'DDD')"));

            RegisterFunction(FunctionType.DateTime.AddSecond, new DateAddFunctionView(DatePartType.Second));
            RegisterFunction(FunctionType.DateTime.AddMinute, new DateAddFunctionView(DatePartType.Minute));
            RegisterFunction(FunctionType.DateTime.AddHour, new DateAddFunctionView(DatePartType.Hour));
            RegisterFunction(FunctionType.DateTime.AddDay, new DateAddFunctionView(DatePartType.Day));
            RegisterFunction(FunctionType.DateTime.AddMonth, new StandardFunctionView("add_months"));
            //RegisterFunction(FunctionType.DateTime.AddMonth, new DateAddFunctionView(DatePartType.Month));
            RegisterFunction(FunctionType.DateTime.AddYear, new DateAddFunctionView(DatePartType.Year));
        }



        enum DatePartType
        {
            Second, Minute, Hour, Day, Month, Year
        }
        class DateAddFunctionView : IFunctionView
        {
            readonly DatePartType Type;
            public DateAddFunctionView(DatePartType type)
            {
                Type = type;
            }

            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args.Length != 2)
                    throw new ArgumentException("function ' dateadd takes 2 arguments.");

                var addPart = (args[1] as SqlInt32).Value;

                visitor.Builder.Append("(");
                visitor.Visit(args[0]);
                visitor.Builder.Append(" + interval '" + addPart.Value.ToString() + "' " + Type.ToString() + ")");
            }
        }

        class DayOfWeekFunctionView : IFunctionView
        {
            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args.Length != 1)
                    throw new ArgumentException("function ' dateadd takes 1 arguments.");

                visitor.Builder.Append("(case ")
                    .Append("when to_char(").Append(() => visitor.Visit(args[0])).Append(",'DY') = 'MON' THEN cast(1 as NUMBER(10,0))")
                    .Append("when to_char(").Append(() => visitor.Visit(args[0])).Append(",'DY') = 'TUE' THEN cast(2  as NUMBER(10,0))")
                    .Append("when to_char(").Append(() => visitor.Visit(args[0])).Append(",'DY') = 'WED' THEN cast(3  as NUMBER(10,0))")
                    .Append("when to_char(").Append(() => visitor.Visit(args[0])).Append(",'DY') = 'THU' THEN cast(4  as NUMBER(10,0))")
                    .Append("when to_char(").Append(() => visitor.Visit(args[0])).Append(",'DY') = 'FRI' THEN cast(5  as NUMBER(10,0))")
                    .Append("when to_char(").Append(() => visitor.Visit(args[0])).Append(",'DY') = 'SAT' THEN cast(6  as NUMBER(10,0))")
                    .Append("when to_char(").Append(() => visitor.Visit(args[0])).Append(",'DY') = 'SUN' THEN cast(7  as NUMBER(10,0))")
                    .Append(" end)");
            }
        }

        #endregion

        public override string FormatCollectionOperation(CollectionOperator type)
        {
            if (type == CollectionOperator.Except)
                return "MINUS";
            return base.FormatCollectionOperation(type);
        }
        public override bool SupportsCollectionOperation(CollectionOperator type)
        {
            return base.SupportsCollectionOperation(type);
        }
        public override void BuildSelect(NodeWriter visitor, SelectNode select)
        {
            if (select == null)
                throw new ArgumentNullException("select");


            if (select.PageIndex <= 0)
            {
                if (select.PageSize > 0)
                    select.Where.AddCriterion(Criterion.Native("ROWNUM <=" + (select.PageIndex + 1) * select.PageSize), LogicOperator.And);
                BuildBasicSelect(visitor,select);
               
            }
            else
                BuildSelectWithOffset(visitor, select);

            visitor.Builder.Buffer.Replace("DISTINCT", "");
        }

        protected virtual void BuildSelectWithOffset(NodeWriter visitor, SelectNode select)
        {
            const string fmt = "SELECT DISTINCT * FROM ( SELECT row_.*, ROWNUM rownum_ FROM({0})row_ WHERE ROWNUM <={1}) WHERE rownum_ >{2}";

            var tmpSelect = select.Clone() as SelectNode;
            tmpSelect.PageSize = -1;
            tmpSelect.PageIndex = -1;
            tmpSelect.Collections.Clear();

            var tmpResult = tmpSelect.Result;

            var sql = new StringBuilder().AppendFormat(fmt
                , tmpResult.Text
                , (select.PageIndex + 1) * select.PageSize
                , select.PageIndex * select.PageSize);

            visitor.Reset(SqlBuilder.Create(tmpResult.Parameters, sql));
        }

        protected void BuildBasicSelect(NodeWriter visitor, SelectNode select)
        {
            if (select.Into != null)
            {
                visitor.Builder.Append("INSERT ");
                visitor.Visit(select.Into);
                visitor.Builder.AppendLine();
            }

            visitor.Builder.Append("SELECT ");

            if (select.IsDistinct)
                visitor.Builder.Append("DISTINCT ");

            if (select.Projections.Count > 0)
                visitor.VisitEnumerable(select.Projections.ToArray());
            else
                visitor.Builder.Append("*");

           

            visitor.Builder.AppendLine().Append("FROM ");
            var table = new TableNode(select.Name, select.Alias);
            table.Accept(visitor);

            if (select.Joins.Count() > 0)
            {
                visitor.Builder.AppendLine();
                visitor.VisitEnumerable(select.Joins.ToArray(), " ");
            }

            select.Where.Accept(visitor);
            select.Group.Accept(visitor);
            select.Having.Accept(visitor);
            select.OrderBy.Accept(visitor);
        }
    }

    public class Oracle9iDialect : Oracle8iDialect
    {
        protected override void RegisterColumnTypes()
        {
            base.RegisterColumnTypes();

            RegisterColumnType(DbTypeCode.Date, "DATE");
            RegisterColumnType(DbTypeCode.DateTime, "TIMESTAMP(4)");
            RegisterColumnType(DbTypeCode.Time, "TIMESTAMP(4)");
        }
    }

    public class Oracle10gDialiect : Oracle9iDialect
    {
    }
}
